一、回顧：第五堂 相關情境練習

(一) 使用資料表：Order 訂單資料表

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

(二) 題目：統計各月份三個員工的訂單業績

統計Order資料表當中，員工1、員工3、員工5 在「1996年的每個月」得到的訂單數量

(三) 參考SQL

SELECT 子查詢 寫法

SELECT  DISTINCT CONCAT(YEAR(OrderDate), '-' ,
        Month(OrderDate)) AS "Year_Month",
        ( SELECT COUNT(*)
          FROM   Orders subQueryTable
          WHERE  EmployeeId = 1 AND
                 YEAR(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                 MONTH(subQueryTable.OrderDate) = MONTH(Orders.OrderDate)) AS "Employee ID 1 have orders",
        ( SELECT COUNT(*)
          FROM  Orders subQueryTable
          WHERE EmployeeId = 3 AND
                YEAR(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                MONTH(subQueryTable.OrderDate) = MONTH(Orders.OrderDate)) AS "Employee ID 3 have orders",
         ( SELECT COUNT(*)
           FROM  Orders subQueryTable
           WHERE EmployeeId = 5 AND
                   YEAR(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                   MONTH(subQueryTable.OrderDate) = MONTH(Orders.OrderDate)) AS "Employee ID 5 have orders"
FROM Orders
WHERE Year(Orders.OrderDate) = 1996

FROM + JOIN 寫法

SELECT AllDateList.OrderDate,
	   IFNULL(Employee01_Result.OrderNum, 0) AS "Employee ID 1 have orders",
       IFNULL(Employee03_Result.OrderNum, 0) AS "Employee ID 3 have orders",
       IFNULL(Employee05_Result.OrderNum, 0) AS "Employee ID 5 have orders"
FROM ( SELECT DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS OrderDate
       FROM Orders
       WHERE YEAR(OrderDate) = 1996 ) AS AllDateList
LEFT JOIN  (SELECT DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS OrderDate,
                   COUNT(*) AS OrderNum
            FROM   Orders
            WHERE  EmployeeId = 1
            GROUP BY CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) ) AS Employee01_Result
ON AllDateList.OrderDate = Employee01_Result.OrderDate
LEFT JOIN  (SELECT DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS OrderDate,
                   COUNT(*) AS OrderNum
            FROM   Orders
            WHERE  EmployeeId = 3
            GROUP BY CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) ) AS Employee03_Result
ON AllDateList.OrderDate = Employee03_Result.OrderDate
LEFT JOIN  (SELECT DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS OrderDate,
                   COUNT(*) AS OrderNum
            FROM   Orders
            WHERE  EmployeeId = 5
            GROUP BY CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) ) AS Employee05_Result
ON AllDateList.OrderDate = Employee05_Result.OrderDate

From 子查詢 和 JOIN 的用法將於後續介紹
LEFT JOIN 後 不存在的資料會是NULL，使用IFNULL可將欄位是NULL的值做轉換

(四) 思路過程 (SELECT 子查詢 寫法)

1. 在 SELECT使用Concate取得所有1996年的「年-月」，接著使用 「DISTINCT 關鍵字」 剔除重複的日期

SELECT  DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS "Year_Month"
FROM Orders
WHERE Year(Orders.OrderDate) = 1996

2. 將每個日期依序帶入子查詢，使用COUNT()計算欄位次數，查詢Empmloyee 1 的訂單數量

SELECT  DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS "Year_Month",
          ( SELECT COUNT(*)
            FROM   Orders subQueryTable
            WHERE  EmployeeId = 1 AND
                   Year(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                   Month(subQueryTable.OrderDate) = Month(Orders.OrderDate)) AS "Employee ID 1 have orders"
FROM Orders
WHERE Year(Orders.OrderDate) = 1996

3.依照思路2的方式，接著取得Employee 3 和 Employee 5的取得的訂單數量

SELECT  DISTINCT CONCAT(YEAR(OrderDate), '-' ,Month(OrderDate)) AS "Year_Month",
        (SELECT COUNT(*)
         FROM   Orders subQueryTable
         WHERE  EmployeeId = 1 AND
                Year(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                Month(subQueryTable.OrderDate) = Month(Orders.OrderDate)) AS "Employee ID 1 have orders",
        (SELECT COUNT(*)
		 FROM  Orders subQueryTable
         WHERE EmployeeId = 3 AND
               Year(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
               Month(subQueryTable.OrderDate) = Month(Orders.OrderDate)) AS "Employee ID 3 have orders",
         (SELECT COUNT(*)
		  FROM  Orders subQueryTable
          WHERE EmployeeId = 5 AND
                Year(subQueryTable.OrderDate) = YEAR(Orders.OrderDate) AND
                Month(subQueryTable.OrderDate) = Month(Orders.OrderDate)) AS "Employee ID 5 have orders"
FROM Orders
WHERE Year(Orders.OrderDate) = 1996

(五) 補充說明：DISTINCT 關鍵字

用途：去除重複的資料

1. 不使用DISTINCT，從訂單(Orders)中的所有年月會有重複

2. 使用DISTINCT，從訂單(Orders)中的所有年月不會有重複

二、第六堂範例：實作最後分工日期一覽表

(一)需求

依序列出每個成員，最後一次「打掃」、「拖地」、「洗碗」和「倒垃圾」的排班日期
讓泰D往後的排班工作分配可以更平均。

(二)實作結果

成員<->項目：

SELECT Family.FamilyName AS 家庭成員,
       CAST(LastDate_CleanItem01.CleanDate AS DATE) AS 最後一次打掃日期,
       CAST(LastDate_CleanItem02.CleanDate AS DATE) AS 最後一次拖地日期,
       CAST(LastDate_CleanItem03.CleanDate AS DATE) AS 最後一次洗碗日期,
       CAST(LastDate_CleanItem04.CleanDate AS DATE) AS 最後一次倒垃圾日期
FROM Family
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '0'
            GROUP BY FamilyId) AS LastDate_CleanItem01 --所有成員最後「掃地」的日期
ON Family.FamilyId = LastDate_CleanItem01.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '1'
            GROUP BY FamilyId) AS LastDate_CleanItem02 --所有成員最後「拖地」的日期
ON Family.FamilyId = LastDate_CleanItem02.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '2'
            GROUP BY FamilyId) AS LastDate_CleanItem03 --所有成員最後「洗碗」的日期
ON Family.FamilyId = LastDate_CleanItem03.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '3'
            GROUP BY FamilyId) AS LastDate_CleanItem04 --所有成員最後「倒垃圾」的日期
ON Family.FamilyId = LastDate_CleanItem04.FamilyId

(三)資料來源

1. Family 家庭資料表

• FamilyId：家庭成員ID
• FamilyName：家庭成員姓名
• FamilySex：家庭成員性別

2. CleanItemList 家事項目清單表

• CleanItem：家事項目編號
• ItemName：家事項目名稱

3. CleanSchedule 家事排班表

• CleanScheduleId：家事排班項目ID
• CleanDateTime：家事日期
• FamilyId：負責家事成員ID (對應Family資料表的FamilyId欄位)
• CleanItem：負責清掃項目 (對應CleanItemList的CleanItem欄位)

三、準備資料

模擬演練平台

DB Fiddle (MySQL 5.6)
https://www.db-fiddle.com/

DDL SQL

/* 家庭排班表 範例資料 */

CREATE TABLE `CleanSchedule` (
  `CleanScheduleId` varchar(100) NOT NULL,
  `CleanDateTime` datetime DEFAULT NULL,
  `FamilyId` varchar(100) DEFAULT NULL,
  `CleanItem` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`CleanScheduleId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;


INSERT INTO `CleanSchedule` (`CleanScheduleId`, `CleanDateTime`, `FamilyId`, `CleanItem`) VALUES
	('0FF1B602-580C-4416-AF25-7C0876EEE19D', '2019-08-17 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('1506BC60-C614-4643-B950-8D5F803C13D6', '2019-08-17 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('24653640-14FA-49A2-AC46-DB344719A88B', '2019-08-15 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('2D682CA3-B900-41B0-9AD9-5611296DFBE5', '2019-08-18 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '2'),
	('34931A84-85E7-4236-B1C4-01D190EEFE27', '2019-08-16 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '0'),
	('380A9221-4A2D-41A3-A350-D53175B670C0', '2019-08-15 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('39832DFE-B93F-45D9-99C2-5EEAE76FB5F3', '2019-08-17 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '0'),
	('4B4E212B-C40B-4233-B3B3-3531D6FE7915', '2019-08-18 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '1'),
	('4C534822-2091-4D9F-94FB-FDCB6568E325', '2019-08-15 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '5'),
	('61ED1DE9-5523-4FBA-B5AA-236007479849', '2019-08-15 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('67359081-D70B-4DCE-8011-B796B7516CE3', '2019-08-17 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '5'),
	('71CBD806-D0E4-4E87-8ACF-1BF9995EF69C', '2019-08-16 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '1'),
	('77B08241-1BEA-4550-AD28-B10CC4E39E35', '2019-08-17 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '4'),
	('78E9F96C-4B60-4ACB-910B-AC6E48AE0270', '2019-08-17 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '3'),
	('7BE1E533-FDE3-40EA-9A4F-E7638BA1E168', '2019-08-18 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('83595DE1-12FE-453D-9DDD-6ED7D0A355F4', '2019-08-18 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '4'),
	('843D27D9-CB11-446B-AF65-563C641D872B', '2019-08-16 00:00:00', '3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '3'),
	('9743E8D4-596A-4F0F-85B1-9533ED193784', '2019-08-15 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '2'),
	('C5DDA457-9C99-42A1-B9AD-271C9DD974F9', '2019-08-15 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '1'),
	('C78F99F6-37B6-4FD5-AD04-D586BF032D1C', '2019-08-16 00:00:00', 'bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '5'),
	('D420FFDD-6BAE-435F-85AE-57D917EA63ED', '2019-08-18 00:00:00', '91b18f1f-4ef8-4066-97c4-28daea585db5', '3'),
	('F802E6A4-5F10-4985-BFD2-0D5C4435EF6A', '2019-08-18 00:00:00', 'de8cb5db-2061-4d35-a662-ba5f528fadba', '0'),
	('FA6FC631-97B1-4A9E-807D-2B520ACB7D28', '2019-08-16 00:00:00', '91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '2'),
	('FF257219-DB3E-4CBB-8E63-C5A85B09950A', '2019-08-16 00:00:00', '34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '4');

/* 家庭成員資料表 範例資料 */
CREATE TABLE IF NOT EXISTS `Family` (
  `FamilyId` varchar(100) NOT NULL,
  `FamilyName` varchar(10) DEFAULT NULL,
  `FamilySex` varchar(10) DEFAULT NULL,
  `BirthDate` datetime DEFAULT NULL,
  `PhoneNumber` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`FamilyId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `Family` (`FamilyId`, `FamilyName`, `FamilySex`, `BirthDate`, `PhoneNumber`) VALUES
	('34bf1b6f-191d-40e9-9a8c-3c282e6a700d', '泰肝', '女生', '2007-10-11 00:00:00', ''),
	('3ad93ba4-c799-4a32-ac2e-8abc74dd6375', '泰熱', '男生', '2001-12-10 00:00:00', '0934567890'),
	('91b18f1f-4ef8-4066-97c4-28daea585db5', '泰胖', '女生', '2003-05-13 00:00:00', ''),
	('91dcde4b-10b3-421e-ab8e-bb6bc23b4350', '泰瘦', '女生', '1985-07-10 00:00:00', '0944623456'),
	('bce0e4ae-ac70-4131-aa6f-d1e25b87fad9', '泰冷', '男生', '1995-01-23 00:00:00', '0977654258'),
	('de8cb5db-2061-4d35-a662-ba5f528fadba', '泰賢', '男生', '1977-02-03 00:00:00', '0944589456');

/* 家事項目清單  */
CREATE TABLE IF NOT EXISTS `CleanItemList` (
  `CleanItem` varchar(50) NOT NULL,
  `ItemName` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`CleanItem`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `CleanItemList` (`CleanItem`, `ItemName`) VALUES
	('0', '打掃'),
	('1', '拖地'),
	('2', '洗碗'),
	('3', '倒垃圾');
    

四、觀念理解：INNER JOIN 與 LEFT JOIN

(一) 何謂JOIN

將來自不同地方的資料合併在一起。常見的關聯方式：

SELECT *
FROM 表1
XXXX JOIN 表2
ON 表1.共同欄位 = 表2.共同欄位

可被JOIN的資料來源包括：

• 資料表
• 子查詢

(二) LEFT JOIN ： 如果找不到合併的資料，「保留」左方被合併的資料

「泰熱」、「泰瘦」、「泰冷」沒有被排過任何一次打掃工作，所以使用LEFT JOIN後，被合併的資料會以NULL呈現

SELECT Family.FamilyName AS 成員,
       CAST(LastDate_CleanItem01.CleanDate AS DATE) AS 最後一次打掃日期
FROM Family
LEFT JOIN (
             SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                    FamilyId
             FROM CleanSchedule
             WHERE CleanItem  = '0'
      GROUP BY FamilyId
           ) AS LastDate_CleanItem01 
ON Family.FamilyId = LastDate_CleanItem01.FamilyId;

(三) INNER JOIN ： 如果找不到右方合併的資料，「不要保留」左方被合併的資料

若改用INNER JOIN以後，因為「泰熱」、「泰瘦」和「泰冷」在排班表沒有任何「打掃的工作」，所以成員名單也不會出現他們兩個人的資料

SELECT Family.FamilyName AS 成員,
       CAST(LastDate_CleanItem01.CleanDate AS DATE) AS 最後一次打掃日期
FROM Family
INNER JOIN (
             SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                    FamilyId
             FROM CleanSchedule
             WHERE CleanItem  = '0'
             GROUP BY FamilyId
           ) AS LastDate_CleanItem01 
ON Family.FamilyId = LastDate_CleanItem01.FamilyId;

五、FROM 子查詢 + JOIN 跟 SELECT 子查詢 的差別

(一) FROM 子查詢的思維：

1. 分類、查詢資料: 在FROM 整理資料分類、查詢各個分類資料內容
2. 合併查詢資料: 使用JOIN 將所有的查詢結果合併在一起

FROM Family
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '0'
            GROUP BY FamilyId) AS LastDate_CleanItem01 --所有成員最後「掃地」的日期
ON Family.FamilyId = LastDate_CleanItem01.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '1'
            GROUP BY FamilyId) AS LastDate_CleanItem02 --所有成員最後「拖地」的日期
ON Family.FamilyId = LastDate_CleanItem02.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '2'
            GROUP BY FamilyId) AS LastDate_CleanItem03 --所有成員最後「洗碗」的日期
ON Family.FamilyId = LastDate_CleanItem03.FamilyId
LEFT JOIN ( SELECT MAX(CAST(CleanDateTime AS DATE)) AS CleanDate,
                   FamilyId
            FROM CleanSchedule
            WHERE CleanItem  = '3'
            GROUP BY FamilyId) AS LastDate_CleanItem04 --所有成員最後「倒垃圾」的日期
ON Family.FamilyId = LastDate_CleanItem04.FamilyId

3. 選擇需要的資料： 在SELECT 選擇 FROM 與 JOIN 查詢到的資料欄位

SELECT Family.FamilyName AS 家庭成員,
       CAST(LastDate_CleanItem01.CleanDate AS DATE) AS 最後一次打掃日期,
       CAST(LastDate_CleanItem02.CleanDate AS DATE) AS 最後一次拖地日期,
       CAST(LastDate_CleanItem03.CleanDate AS DATE) AS 最後一次洗碗日期,
       CAST(LastDate_CleanItem04.CleanDate AS DATE) AS 最後一次倒垃圾日期

(二) SELECT 子查詢

1. 取得所有的資料
2. 逐筆分類資料：一筆一筆的將資料帶入子查詢，進行分類

(三) 十萬筆 VS 百萬筆 資料 的查詢次數

SELECT 子查詢 > FROM 子查詢 + JOIN

原因：有幾筆資料，就會做幾次SELECT 子查詢，FROM 子查詢的執行次數固定不變。
當資料一大，SELECT 子查詢的效能就會變得很差。

六、延伸閱讀：JOIN

MySQL 超新手入門（5）JOIN 與 UNION 查詢
http://www.codedata.com.tw/database/mysql-tutorial-5-join-union

七、變化練習

將範例的呈現方式，從「成員<->項目」改成「項目<->成員

八、相關情境練習

使用資料表：訂單表頭資料(Orders)、員工資料(Employees)

https://www.w3schools.com/sql/trymysql.asp?filename=trysql_func_mysql_datediff

題目:員工最高業績月份

從Orders 訂單表頭所有的資料當中，找出每一個員工在哪一個月訂單數目最高，那個月有幾筆訂單，並依照最高的訂單數量由高到低列出

列出欄位：

• 員工完整姓名(Employees.Last Name + Employees.FirstName)
• 最高的訂單月份
• 最高的訂單數量

結果示意圖

九、 第六堂總結

SELECT 查詢的執行先後順序

1. FROM 資料來源
2. ON 資料合併的關聯條件
3. JOIN 關聯合併不同來源的資料
4. WHERE 過濾需要的資料
5. GROUP BY 統計前的資料分類
6. WITH CUBE 或 WITH ROLLUP
7. HAVING 統計後的資料過濾
8. WINDOW functions
9. SELECT 需要的欄位
10. DISTINCT 剔除重複的資料
11. ORDER BY 用什麼欄位決定資料的呈現順序
12. TOP (LIMIT / OFFSET)